package org.jeecg.modules.demo.mp.util;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    /**
     * 查询记录
     * @param jdbcTemplate
     * @param sql
     * @param sqlParams
     * @return
     */
    public static List<Map> executeQuery(JdbcTemplate jdbcTemplate, String sql, List<Object> sqlParams) {

//        jdbcTemplate.queryforlis

        List<Map> result = jdbcTemplate.query(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                if(sqlParams !=null && !sqlParams.isEmpty()){
                    for (int i = 0; i <= sqlParams.size(); i++) {
                        Object v = sqlParams.get(i);
                        if(v==null){

                        }else if(v instanceof  String) {
                            ps.setString(i+1, String.valueOf(v));
                        }else if(v instanceof BigDecimal) {
                            ps.setBigDecimal(i+1, (BigDecimal) v);
                        }
                    }
                }
            }
        }, new ResultSetExtractor<List<Map>>() {
            public List<Map> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Map> list = new ArrayList<Map>();
                ResultSetMetaData rsmd = rs.getMetaData();
                HashMap<String, Object> map;
                while (rs.next()) {
                    int numColumns = rsmd.getColumnCount();
                    map = new HashMap<String, Object>();
                    for (int i = 1; i <= numColumns; i++) {
                        String colName = rsmd.getColumnName(i);
                        if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
                            map.put(colName, rs.getArray(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
                            map.put(colName, rs.getInt(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
                            map.put(colName, rs.getBoolean(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
                            map.put(colName, rs.getBlob(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
                            map.put(colName, rs.getDouble(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
                            map.put(colName, rs.getFloat(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
                            map.put(colName, rs.getInt(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
                            map.put(colName, rs.getNString(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
                            map.put(colName, rs.getString(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
                            map.put(colName, rs.getInt(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
                            map.put(colName, rs.getInt(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
                            map.put(colName, rs.getDate(colName));
                        } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
                            map.put(colName, rs.getTimestamp(colName));
                        } else {
                            map.put(colName, rs.getObject(colName));
                        }
                    }
                    list.add(map);
                }

                return list;
            }
        });


        return result;
    }
}
